﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
using Settings;

namespace Business
{
    public class Subscriber_ClassificationRepository : BaseBusiness
    {
        public static void Delele(DataTable dtGuidKey, SqlCommand cmd)
        {
            if (dtGuidKey.Rows.Count > 0)
            {
                cmd.Parameters.Clear();
                cmd.CommandText = "sp_crm_subscriber_classification_Delete";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@GuidKey", dtGuidKey);
                cmd.Parameters["@GuidKey"].SqlDbType = SqlDbType.Structured;
                cmd.Parameters["@GuidKey"].TypeName = "GuidKey_type";
                cmd.ExecuteNonQuery();
            }
        }

        public IList<crm_subscriber_classification> GetAll()
        {
            return DB.crm_subscriber_classifications.ToList();
        }

        public IList<crm_subscriber_classification> GetAll(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = DB.crm_subscriber_classifications
                                .OrderBy(p => p.seq_id);
            totalRecord = retValue.Count();
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<crm_subscriber_classification> GetByCollection(crm_collection colection)
        {
            if (colection == null || colection.collection_value == null)
                return null;
            return DB.ExecuteQuery<crm_subscriber_classification>(GetQuery(colection)).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvExpired()
        {
            try
            {
                List<crm_subscriber_classification> retValue = new List<crm_subscriber_classification>();
                var collections = DB.crm_collections
                                .Where(cc => DB.crm_advertisments
                                        .Where(ca => ca.end_expected < DateTime.Now.AddHours(Settings.LocalSettings.TimeExpired) 
                                            //&& ca.end_expected > DateTime.Now.AddHours(-24)
                                            && ca.action_id == (int)ActionId.WhenMessageReceived)
                                        .Select(ca => ca.collection_id).Contains(cc.collection_id)).ToList();
                if (collections == null) return null;
                foreach (var item in collections)
                {
                    var cols = GetByCollection(item);
                    if (cols != null && cols.Count > 0)
                        retValue.AddRange(cols);
                }
                return retValue.Distinct(new SubscriberClassificationCompare()).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<crm_subscriber_classification> GetByAdvExpired(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = GetByAdvExpired();
            if (retValue == null || retValue.Count == 0)
            {
                totalRecord = 0;
                return null;
            }
            totalRecord = retValue.Count;
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvUnExpired()
        {
            List<crm_subscriber_classification> retValue = new List<crm_subscriber_classification>();
            var collections = DB.crm_collections
                            .Where(cc => DB.crm_advertisments
                                    .Where(ca => ca.end_expected >= DateTime.Now && ca.action_id == (int)ActionId.WhenMessageReceived)
                                    .Select(ca => ca.collection_id).Contains(cc.collection_id)).ToList();
            if (collections == null) return null;
            foreach (var item in collections)
            {
                var cols = GetByCollection(item);
                if (cols != null && cols.Count > 0)
                    retValue.AddRange(cols);
            }
            return retValue.Distinct(new SubscriberClassificationCompare()).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvUnExpired(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = GetByAdvUnExpired();
            if (retValue == null || retValue.Count == 0)
            {
                totalRecord = 0;
                return null;
            }
            totalRecord = retValue.Count;
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public crm_subscriber_classification GetById(Guid Id)
        {
            return DB.crm_subscriber_classifications.FirstOrDefault(p => p.guid_key == Id);
        }

        public void DeleteById(Guid Id)
        {
            var entity = DB.crm_subscriber_classifications.FirstOrDefault(p => p.guid_key == Id);
            if (entity != null)
            {
                DB.crm_subscriber_classifications.DeleteOnSubmit(entity);
                DB.SubmitChanges();
            }
        }

        public int DeleteRecordExpired()
        {
            try
            {
                //DataTable table = new DataTable();
                //table.Columns.Add("guid_key", typeof(Guid));
                //var expiredList = GetByAdvExpired();
                //if (expiredList == null || expiredList.Count == 0) return 0;
                //foreach (var item in expiredList)
                //{
                //    table.Rows.Add(item.guid_key);
                //}
                //using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
                //{
                //    conn.Open();
                //    using (SqlCommand cmd = conn.CreateCommand())
                //    {
                //        cmd.CommandType = CommandType.StoredProcedure;
                //        cmd.CommandText = "sp_crm_subscriber_classification_Delete";
                //        cmd.Parameters.AddWithValue("@GuidKey", table);
                //        cmd.Parameters["@GuidKey"].SqlDbType = SqlDbType.Structured;
                //        cmd.Parameters["@GuidKey"].TypeName = "dbo.GuidKey_type";
                //        cmd.ExecuteNonQuery();
                //        return 1;
                //    }
                //}
                using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "DELETE FROM crm_subscriber_classification WHERE received_time < @datetime";
                        cmd.Parameters.AddWithValue("@datetime", DateTime.Now.AddMinutes(-Settings.LocalSettings.TimeExpired));
                        cmd.ExecuteNonQuery();
                        return 1;
                    }
                }

            }
            catch (Exception e)
            {
                new LogRepository().InsertLog(e);
                return 0;
            }
        }

        public string GetQuery(crm_collection entity)
        {
            XElement element = entity.collection_value;
            StringBuilder retValue = new StringBuilder("SELECT * ");
            string fromClause = element.Element("from").Value;
            string whereClause = element.Element("where").Value;
            retValue.AppendLine("FROM " + fromClause);
            if (!string.IsNullOrEmpty(whereClause))
                retValue.AppendLine("WHERE " + whereClause);
            return retValue.ToString();
        }
    }
}
